<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use app\admin\model\Project;
use think\Db;
use Exception;
use think\exception\DbException;
use think\exception\PDOException;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use fast\Tree;

/**
 * 清单库管理
 *
 * @icon fa fa-circle-o
 */
class ProjectList extends Backend
{

    /**
     * Project模型对象
     * @var \app\admin\model\ProjectItems
     */
    protected $model = null;

    protected $projectConfig = null;
    protected $searchFields = 'name,admin.nickname';
    protected $noNeedRight = ['import_preview', 'getListByProject'];
    protected $excelTitleMapping = [
        'num' => ['序号'],
        'name' => ['项目名称'],
        'code' => ['项目编码'],
        'description' => ['项目特征描述'],
        'unit' => ['计量单位'],
        'quantity' => ['工程量'],
        'unit_price' => ['综合单价'],
        'total_price' => ['合价', '综合合价'],
        'appraise_price' => ['其中暂估价', '其中：暂估价'],
    ];
    //排除标题
    protected $excludeTitle = ['分部分项工程费', '单价措施项目费', '单价措施合计', '分部分项合计'];
    protected $mergeTitle = ['金额（元）', '金额(元)'];
    protected $leveloneTitle = ['工程名称：'];
    protected $excelLimit = 1500;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\ProjectItems;
        $config = DB::name('config')->where('group', 'caijiaku')->column('value', 'name');
        $config = array_map(function ($item) {
            return json_decode($item, true);
        }, $config);
        $this->projectConfig = $config;
        $this->view->assign('projectConfig', $this->projectConfig);
        $this->assignconfig('projectConfig', $config);
        $action = strtolower($this->request->action());
        if (in_array($action, ['index', 'getlistbyproject'])) {
            $this->dataLimit = false;
        } else {
            $this->dataLimit = 'auth';
        }
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

     /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if (false === $this->request->isAjax()) {
            return $this->view->fetch();
        }
        //如果发送的来源是 Selectpage，则转发到 Selectpage
        if ($this->request->request('keyField')) {
            return $this->selectpage();
        }
        [$where, $sort, $order, $offset, $limit] = $this->buildparams();
        $wheres = [];
        //排除超管数据
        if(!$this->auth->isSuperAdmin()) {
            //排除超级管理员
            $groupWhere = [
                'caijiakuid' => CAIJIAKU_ID,
                'status' => 'normal',
                'rules' => '*',
            ];
            $groupIds = Db::name('auth_group')->where($groupWhere)->column('id');
            $adminIds = Db::name('auth_group_access')->where(['group_id' => ['in', $groupIds]])->column('uid');
            $wheres['project_items.admin_id'] = ['not in', $adminIds];
        }
        if ($this->request->request('ids')) {
            $wheres['project_items.id'] = ['in', $this->request->request('ids')];
        }
        $list = $this->model
            ->with(['admin', 'project'])
            ->where($where)
            ->where($wheres)
            ->order($sort, $order)
            ->paginate($limit);
            foreach ($list as $row) {
                $row->getRelation('project')->visible(['project_name','project_address','pricing_date','project_class','project_bulid_cost','project_scale','square_build_cost','build_unit','construction_unit','structure_type','build_cost_type','build_form','taxation_type','contract_no','contract_name','createtime']);
                $row->getRelation('admin')->visible(['nickname']);
                // $row->visible(['id','admin_id','project_name','project_address','pricing_date','project_class','project_bulid_cost','project_scale','square_build_cost','build_unit','construction_unit','structure_type','build_cost_type','build_form','taxation_type','contract_no','contract_name','createtime', 'admin.nickname', 'updatetime']);
            }
        $result = ['total' => $list->total(), 'rows' => $list->items()];
        return json($result);
    }

    public function getListByProject()
    {
        $ids = $this->request->param("id");
        $where = [
            'project_id' => $ids,
        ];
        $list = $this->model->where($where)->field('id,name,code,description,quantity,unit,unit_price,total_price,appraise_price,level,pid, 0 as haschild')->select();
        if ($list) {
            // 使用哈希表记录父子关系
            $childrenMap = [];
            foreach ($list as $item) {
                $childrenMap[$item['pid']][] = $item['id'];
            }

            // 遍历判断是否有子级
            foreach ($list as &$v) {
                $v['haschild'] = isset($childrenMap[$v['id']]) && !empty($childrenMap[$v['id']]) ? 1 : 0;
            }
        }
        $result = array("total" => count($list), "rows" => $list);

        return json($result);
    }

    public function detail($ids = null)
    {
        $row = $this->model->where(['project_items.id' => $ids])->with([
            'admin',
            'project'
        ])->find();
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $row = $row->toArray();
        $projectFiles = $row['project']['project_files'] ? explode(',', $row['project']['project_files']) : [];
        $row['project']['project_files_arr'] = array_map(function ($item) {
            return cdnurl($item);
        }, $projectFiles);
        $contractFiles = $row['project']['contract_files'] ? explode(',', $row['project']['contract_files']) : [];
        $row['project']['contract_files_arr'] = array_map(function ($item) {
            return cdnurl($item);
        }, $contractFiles);
        $preparedFiles = $row['project']['prepared_files'] ? explode(',', $row['project']['prepared_files']) : [];
        $row['project']['prepared_files_arr'] = array_map(function ($item) {
            return cdnurl($item);
        }, $preparedFiles);
        $this->view->assign('row', $row);
        $this->view->assign('ids', $ids);
        return $this->view->fetch();
    }

    /**
     * 添加
     *
     * @return string
     * @throws \think\Exception
     */
    public function add()
    {
        $project_id = $this->request->param('project_id');
        if (false === $this->request->isPost()) {
            $this->view->assign('project_id', $project_id);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);

        if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
            $params[$this->dataLimitField] = $this->auth->id;
        }

        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                $this->model->validateFailException()->validate($validate);
            }
            $result = $this->model->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result === false) {
            $this->error(__('No rows were inserted'));
        }
        $this->success();
    }

    /**
     * 编辑
     *
     * @param $ids
     * @return string
     * @throws DbException
     * @throws \think\Exception
     */
    public function edit($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds) && !in_array($row[$this->dataLimitField], $adminIds)) {
            $this->error(__('You have no permission'));
        }
        if (false === $this->request->isPost()) {
            $this->view->assign('row', $row);
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');
        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $params = $this->preExcludeFields($params);
        $result = false;
        Db::startTrans();
        try {
            //是否采用模型验证
            if ($this->modelValidate) {
                $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                $row->validateFailException()->validate($validate);
            }
            $result = $row->allowField(true)->save($params);
            Db::commit();
        } catch (ValidateException|PDOException|Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if (false === $result) {
            $this->error(__('No rows were updated'));
        }
        $this->success();
    }

    public function import()
    {
        $project_id = $this->request->param('project_id');
        if ($this->request->isPost() == false) {
            $projectInfo = Project::find($project_id);
            $this->view->assign('info', $projectInfo);
            $this->view->assign('excelTitleMapping', $this->excelTitleMapping);
            return $this->view->fetch();
        }
        set_time_limit(0);
        $file = $this->request->request('file', '');
        $curtimes = $this->request->request("curtimes/d", 0);//分批量请求
        $lastrow = $this->request->request("lastrow/d", 0);//上次最后读的行数
        $project_id = $this->request->request("project_id/d", 0);//上次最后读的行数
        if (empty($file)) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        if (empty($project_id)) {
            $this->error(__('Parameter %s can not be empty', 'project_id'));
        }
        $filePath = ROOT_PATH . DS .'public'. $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        if (!file_exists($filePath)) {
            $this->error('文件不存在');
        }

        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }


        if (!$PHPExcel = $reader->load($filePath)) {
            $this->error(__('Unknown data format'));
        }
        $currentSheet = $PHPExcel->getSheet(0);
        $allColumn = $currentSheet->getHighestDataColumn();
        $allRow = $currentSheet->getHighestRow();
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
        $limit = $this->excelLimit;
        $firstnum = $lastrow + 1;
        $fields = [];
        $nameField = $codeField = $unitField = $quantityField = $unitPriceField = $totalPriceField =  $descriptionField = $appraisePriceField = $numField = '';
        $isStart = false;//是否开始读取数据
        $isFirstLine = false;//是否第一行就是表格标题
        $levelOne = '';
        //获取标题
        for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : trim($val);
            }
            
            //判断当前行是否列表开始
            if (!$isStart) {
                //判断是否有标题
                $nameMapping = array_intersect($values, $this->excelTitleMapping['name']);
                if (!empty($nameMapping)) {
                    //判断是否有标题
                    $nameMapping = array_intersect($values, $this->excelTitleMapping['name']);
                    if (!empty($nameMapping)) {
                        $isStart = true;
                        $mergeMapping = array_intersect($values, $this->mergeTitle);
                        if (!empty($mergeMapping)) {
                            $currentRow += 1;
                        }
                        if (empty($fields)) {
                            $isFirstLine = $currentRow == 1 ? true : false;
                            $fields = $values;
                            //获取二级表头
                            if (!empty($mergeMapping)) {
                                //获取mergeMapping 第一个key
                                $key = array_keys($mergeMapping)[0];
                                //将二级表头替换掉一级表头
                                for ($currentColumn = $key+1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                                    $fields[$currentColumn - 1] = is_null($val) ? '' : trim($val);
                                }
                            }
                            $fields = array_map(function ($item) {
                                return $this->trimall($item);
                            }, $fields);
                            // var_dump($fields);
                            //数组交集
                            $nameMapping = array_intersect($fields, $this->excelTitleMapping['name']);
                            $codeMapping = array_intersect($fields, $this->excelTitleMapping['code']);
                            $descriptionMapping = array_intersect($fields, $this->excelTitleMapping['description']);
                            $unitMapping = array_intersect($fields, $this->excelTitleMapping['unit']);
                            $quantityMapping = array_intersect($fields, $this->excelTitleMapping['quantity']);
                            $unitPriceMapping = array_intersect($fields, $this->excelTitleMapping['unit_price']);
                            $totalPriceMapping = array_intersect($fields, $this->excelTitleMapping['total_price']);
                            $appraisePriceMapping = array_intersect($fields, $this->excelTitleMapping['appraise_price']);
                            $numMapping = array_intersect($fields, $this->excelTitleMapping['num']);
                            // var_dump(empty($nameMapping)); var_dump(empty($unitMapping)); var_dump(empty($unitPriceMapping));
                            if(empty($nameMapping) || empty($unitMapping) || empty($unitPriceMapping)){
                                $this->error(__('文档识别失败，请确保文档中必须包含：'.implode('/',$this->excelTitleMapping['name']).'，'.implode('/',$this->excelTitleMapping['unit_price']).'，'.implode('/',$this->excelTitleMapping['unit']).'三个必填信息'));
                            }
                            $nameField = array_values($nameMapping)[0];
                            $codeField = array_values($codeMapping)[0];
                            $descriptionField = array_values($descriptionMapping)[0];
                            $unitField = array_values($unitMapping)[0];
                            $quantityField = !empty($quantityMapping) ? array_values($quantityMapping)[0] : '';
                            $unitPriceField = !empty($unitPriceMapping) ? array_values($unitPriceMapping)[0] : '';
                            $totalPriceField = !empty($totalPriceMapping) ? array_values($totalPriceMapping)[0] : '';
                            $appraisePriceField = !empty($appraisePriceMapping) ? array_values($appraisePriceMapping)[0] : '';
                            $numField = !empty($numMapping) ? array_values($numMapping)[0] : '';
                        }
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
            }
        }
        $data = [];
        $lastlevel = $this->request->request("lastlevel/d", 0);
        $lastfirstpid = $this->request->request("lastfirstpid/d", 0);
        $lastlevelonetitle = $this->request->request("lastlevelonetitle", '');
        $lasttwopid = $this->request->request("lasttwopid/d", 0);
        $lastpid = $this->request->request("lastpid/d", 0);
        // $lastpath = $this->request->request("lastpath", '');
        $count = 0;
        Db::startTrans();
        try {
            for ($currentRow = $firstnum; $currentRow <= $allRow; $currentRow++) {
                $lastrow = $currentRow;
                if (count($data) >= $limit) {
                    break;
                }
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : trim($val);
                }
                //判断标题是否含有leveloneTitle strpos 单项
                $leveloneTitle = $this->leveloneTitle;
                $leveloneMapping = array_filter($values, function($item) use ($leveloneTitle) {
                    $exists = array_filter($leveloneTitle, function($title) use ($item) {
                        return strpos($item, $title) !== false;
                    });
                    return !empty($exists);
                });
                if (!empty($leveloneMapping)) {
                    $levelOneArr = explode('  ', array_shift($leveloneMapping));
                    $thislevelOne = explode('：', $levelOneArr[0])[1] ?? '';
                    if (!empty($thislevelOne) && $thislevelOne != $lastlevelonetitle) {
                        $row = [];
                        $row['caijiakuid'] = CAIJIAKU_ID;
                        $row['admin_id'] = $this->auth->id;
                        $row['project_id'] = $project_id;
                        $row['name'] = $thislevelOne;
                        $row['code'] = '';
                        $row['description'] = '';
                        $row['quantity'] = '';
                        $row['unit'] = '';
                        $row['unit_price'] = 0;
                        $row['total_price'] = 0;
                        $row['appraise_price'] = 0;
                        $row['createtime'] = time();
                        $row['updatetime'] = time();
                        $row['level'] = 1;
                        $insertId = Db::name('project_items')->insertGetId($row);
                        if (!$insertId) {
                            Db::rollback();
                            $this->error(__('导入失败，请检查数据格式'));
                        }
                        $lastlevelonetitle = $thislevelOne;
                        $lastfirstpid = $insertId;
                        $lastlevel = 1;
                        continue;
                    }
                }

                $isStart = true;
                $temp = array_combine($fields, $values);
                
                if(empty($temp[$nameField]) || $temp[$nameField] == $nameField || in_array($temp[$nameField], $this->excludeTitle)){
                    continue;
                }
    
                $row = [];
                $row['caijiakuid'] = CAIJIAKU_ID;
                $row['admin_id'] = $this->auth->id;
                $row['project_id'] = $project_id;
                $row['name'] = $temp[$nameField] ?? '';
                $row['code'] = $temp[$codeField] ?? 0;
                $row['description'] = $temp[$descriptionField] ?? '';
                $row['quantity'] = $quantityField ? $temp[$quantityField] : '';
                $row['unit'] = $temp[$unitField] ?? '';
                $row['unit_price'] = $unitPriceField ? $temp[$unitPriceField] : 0;
                $row['total_price'] = $totalPriceField ? $temp[$totalPriceField] : 0;
                $row['appraise_price'] = $appraisePriceField ? $temp[$appraisePriceField] : 0;
                $row['createtime'] = time();
                $row['updatetime'] = time();
                if (empty($temp[$numField]) && empty($temp[$codeField]) && empty($temp[$unitPriceField])) {
                    $row['level'] = 2;
                    $row['pid'] = $lastfirstpid;
                    $row['path'] = $lastfirstpid;
                } else if (!empty($temp[$numField]) && !empty($temp[$codeField]) && !empty($temp[$unitPriceField])) {
                    $row['level'] = 3;
                    $row['pid'] = $lasttwopid;
                    $row['path'] = $lastfirstpid.','.$lasttwopid;
                } else if (empty($temp[$numField]) && !empty($temp[$codeField]) && !empty($temp[$unitPriceField])) {
                    $row['level'] = 4;
                    $row['pid'] = $lastpid;
                    $row['path'] = $lastfirstpid.','.$lasttwopid.','.$lastpid;
                }
                // if ($row['level'] > 1) {
                //     $row['pid'] = $row['level'] == 2 ? $lastfirstpid : $lastpid;
                //     $row['path'] = $lastpath . ($row['level'] == 3 ?  (',' . $lastpid) : '');
                // }
                $insertId = Db::name('project_items')->insertGetId($row);
                if (!$insertId) {
                    Db::rollback();
                    $this->error(__('导入失败，请检查数据格式'));
                }
                $count += 1;
                if ($row['level'] == 1) {
                    $lastlevel = 1;
                    $lastpid = $insertId;
                    $lastfirstpid = $insertId;
                    // $lastpath = $insertId;
                } else if ($row['level'] == 2) {
                    $lastlevel = 2;
                    $lasttwopid = $insertId;
                } else if ($row['level'] == 3) {
                    $lastlevel = 3;
                    $lastpid = $insertId;
                } else if ($row['level'] == 4) {
                    $lastlevel = 4;
                }
            }
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        $this->success('成功导入数据:' . $count . '条', '', ['lastrow' => $lastrow, 'lastlevel' => $lastlevel, 'lastfirstpid' => $lastfirstpid,'lasttwopid' => $lasttwopid,'lastpid' => $lastpid]);



        // $data = [];
        // for ($currentRow = $firstnum; $currentRow <= $allRow; $currentRow++) {
        //     $lastrow = $currentRow;
        //     if (count($data) >= $limit) {
        //         break;
        //     }
        //     $values = [];
        //     for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
        //         $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
        //         $values[] = is_null($val) ? '' : trim($val);
        //     }
        //     $isStart = true;
        //     $temp = array_combine($fields, $values);
            
        //     if(empty($temp[$nameField]) || $temp[$nameField] == $nameField || in_array($temp[$nameField], $this->excludeTitle)){
        //         continue;
        //     }

        //     $row = [];
        //     $row['caijiakuid'] = CAIJIAKU_ID;
        //     $row['admin_id'] = $this->auth->id;
        //     $row['project_id'] = $project_id;
        //     $row['name'] = $temp[$nameField] ?? '';
        //     $row['code'] = $temp[$codeField] ?? 0;
        //     $row['description'] = $temp[$descriptionField] ?? '';
        //     $row['quantity'] = $quantityField ? $temp[$quantityField] : '';
        //     $row['unit'] = $temp[$unitField] ?? '';
        //     $row['unit_price'] = $unitPriceField ? $temp[$unitPriceField] : 0;
        //     $row['total_price'] = $totalPriceField ? $temp[$totalPriceField] : 0;
        //     $row['appraise_price'] = $appraisePriceField ? $temp[$appraisePriceField] : 0;
        //     $row['createtime'] = time();
        //     $row['updatetime'] = time();
        //     if (empty($temp[$numField]) && empty($temp[$codeField]) && empty($temp[$unitPriceField])) {
        //         $row['level'] = 1;
        //     } else if (!empty($temp[$numField]) && !empty($temp[$codeField]) && !empty($temp[$unitPriceField])) {
        //         $row['level'] = 2;
        //     } else if (empty($temp[$numField]) && !empty($temp[$codeField]) && !empty($temp[$unitPriceField])) {
        //         $row['level'] = 3;
        //     }
            
        //     $data[$currentRow] = $row;
        // }
        // if ($data) {
        //     $data = array_values($data);
        //     Db::startTrans();
        //     try{
        //         $chunkSize = 200; // 每次插入的数据条数
        //         $dataChunks = array_chunk($data, $chunkSize); // 将数据分割成小数组
        //         foreach ($dataChunks as $chunk) {
        //             $res = Db::name('project_items')->insertAll($chunk); // 分批插入数据
        //         }
        //         Db::commit();
        //     } catch (\Exception $e) {
        //         Db::rollback();
        //         $this->error($e->getMessage());
        //     }
        //     $this->success('成功导入数据:' . count($data) . '条', '', ['lastrow' => $lastrow]);
        // } else {
        //     $this->error('没有数据可导入');
        // }
    }

    public function import_preview()
    {
        $file = $this->request->request('listfile');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //加载文件
        if (!$PHPExcel = $reader->load($filePath)) {
            $this->error(__('Unknown data format'));
        }
        $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
        $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
        $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
        $fields = [];
        $newrowdata = [];
        $nameField = $codeField = $unitField = $quantityField = $unitPriceField = $totalPriceField =  $descriptionField = $appraisePriceField = '';
        $isStart = false;//是否开始读取数据
        $isFirstLine = false;//是否第一行就是表格标题
        $levelOne = '';
        for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
            $values = [];
            for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                $values[] = is_null($val) ? '' : trim($val);
            }
            //判断标题是否含有leveloneTitle strpos
            $leveloneTitle = $this->leveloneTitle;
            $leveloneMapping = array_filter($values, function($item) use ($leveloneTitle) {
                $exists = array_filter($leveloneTitle, function($title) use ($item) {
                    return strpos($item, $title) !== false;
                });
                return !empty($exists);
            });
            if (!empty($leveloneMapping)) {
                $levelOneArr = explode('  ', array_shift($leveloneMapping));
                $thislevelOne = explode('：', $levelOneArr[0])[1] ?? '';
                if ($thislevelOne != $levelOne) {
                    $levelOne = $thislevelOne;
                    $data = [];
                    $data['name'] = $levelOne;
                    $data['code'] = '';
                    $data['description'] = '';
                    $data['quantity'] = '';
                    $data['unit'] = '';
                    $data['unit_price'] = '-';
                    $data['total_price'] = '-';
                    $data['appraise_price'] = '-';
                    $data['hang'] = $currentRow;
                    $newrowdata[$currentRow] = $data;
                }
            }
            // var_dump($values);
            // 判断当前行是否列表开始
            if (!$isStart) {
                //判断是否有标题
                $nameMapping = array_intersect($values, $this->excelTitleMapping['name']);
                if (!empty($nameMapping)) {
                    $isStart = true;
                    $mergeMapping = array_intersect($values, $this->mergeTitle);
                    if (!empty($mergeMapping)) {
                        $currentRow += 1;
                    }
                    if (empty($fields)) {
                        $isFirstLine = $currentRow == 1 ? true : false;
                        $fields = $values;
                        //获取二级表头
                        if (!empty($mergeMapping)) {
                            //获取mergeMapping 第一个key
                            $key = array_keys($mergeMapping)[0];
                            //将二级表头替换掉一级表头
                            for ($currentColumn = $key+1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                                $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                                $fields[$currentColumn - 1] = is_null($val) ? '' : trim($val);
                            }
                        }
                        $fields = array_map(function ($item) {
                            return $this->trimall($item);
                        }, $fields);
                        // var_dump($fields);
                        //数组交集
                        $nameMapping = array_intersect($fields, $this->excelTitleMapping['name']);
                        $codeMapping = array_intersect($fields, $this->excelTitleMapping['code']);
                        $descriptionMapping = array_intersect($fields, $this->excelTitleMapping['description']);
                        $unitMapping = array_intersect($fields, $this->excelTitleMapping['unit']);
                        $quantityMapping = array_intersect($fields, $this->excelTitleMapping['quantity']);
                        $unitPriceMapping = array_intersect($fields, $this->excelTitleMapping['unit_price']);
                        $totalPriceMapping = array_intersect($fields, $this->excelTitleMapping['total_price']);
                        $appraisePriceMapping = array_intersect($fields, $this->excelTitleMapping['appraise_price']);
                        // var_dump(empty($nameMapping)); var_dump(empty($unitMapping)); var_dump(empty($unitPriceMapping));
                        if(empty($nameMapping) || empty($unitMapping) || empty($unitPriceMapping)){
                            $this->error(__('文档识别失败，请确保文档中必须包含：'.implode('/',$this->excelTitleMapping['name']).'，'.implode('/',$this->excelTitleMapping['unit_price']).'，'.implode('/',$this->excelTitleMapping['unit']).'三个必填信息'));
                        }
                        $nameField = array_values($nameMapping)[0];
                        $codeField = array_values($codeMapping)[0];
                        $descriptionField = array_values($descriptionMapping)[0];
                        $unitField = array_values($unitMapping)[0];
                        $quantityField = !empty($quantityMapping) ? array_values($quantityMapping)[0] : '';
                        $unitPriceField = !empty($unitPriceMapping) ? array_values($unitPriceMapping)[0] : '';
                        $totalPriceField = !empty($totalPriceMapping) ? array_values($totalPriceMapping)[0] : '';
                        $appraisePriceField = !empty($appraisePriceMapping) ? array_values($appraisePriceMapping)[0] : '';
                    }
                } else {
                    continue;
                }
            } else {
                $data = [];
                $temp = array_combine($fields, $values);
                if (in_array($temp[$nameField], $this->excludeTitle)) continue;
                // if(empty($temp[$nameField]) || empty($temp[$unitPriceField]) || empty($temp[$unitField])){
                if(empty($temp[$nameField])){
                    $isStart = $isFirstLine ? true : false;
                } else {
                    $data['name'] = $temp[$nameField] ?? '';
                    $data['code'] = $temp[$codeField] ?? 0;
                    $data['description'] = $temp[$descriptionField] ?? '';
                    $data['quantity'] = $quantityField ? $temp[$quantityField] : '';
                    $data['unit'] = $temp[$unitField] ?? '';
                    $data['unit_price'] = $unitPriceField ? $temp[$unitPriceField] : '-';
                    $data['total_price'] = $totalPriceField ? $temp[$totalPriceField] : '-';
                    $data['appraise_price'] = $appraisePriceField ? $temp[$appraisePriceField] : '-';
                    $data['hang'] = $currentRow;
                    $newrowdata[$currentRow] = $data;
                }
            }
        }
        
        if(count($newrowdata)==0){
            $this->error(__('文档识别失败，文档数据为空'));
        }
        $newrowdata = array_values($newrowdata);
        $return = [
            'data' => $newrowdata,
            'count' => count($newrowdata),
            'limit' => $this->excelLimit,
            'batchtimes' => ceil(count($newrowdata) / $this->excelLimit)
        ];
        $this->success('导入成功', '', $return);
    }

    //删除空格
    protected function trimall($str)
    {
        $oldchar = array(" ", "　", "\t", "\n", "\r");
        $newchar = array("", "", "", "", "");
        $str = str_replace($oldchar, $newchar, $str);
        $str =  preg_replace("/(\s|\&nbsp\;|　|\xc2\xa0)/", "", $str);
        return $str;
    }
}
